package br.com.scriptmanager.services.factory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Locale;
import java.util.Map;
import java.util.ResourceBundle;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class DBConection {
	private Connection connection;
	private String sql;
	private Map<String, Object> params = new HashMap<String,Object>();
	private ArrayList<String> paramsPosition = new ArrayList<String>();
	
	private static Connection getConnection(){
		ResourceBundle rs = ResourceBundle.getBundle("DB");
		String driver = rs.getString("driver");
		String uri = rs.getString("uri");
		String usuario = rs.getString("usuario");
		String senha = rs.getString("senha");		
		return getConnection(driver,uri,usuario,senha);
	}

	public static Connection getConnection(String drive, String url, String user, String password){
		try{
			Class.forName( drive );
			return DriverManager.getConnection(url,user,password);
		}catch ( Exception ex ){
			System.err.println( "SQLException: " + ex.getMessage() );
		}
		return null;
	}

	public Connection open(){
		try {
			if( connection == null || connection.isClosed() ){
				connection = getConnection();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}
	
	public void commit(){
		try{
			if( !connection.isClosed() ){
				connection.commit();
			}
		}catch ( Exception ex ){
			System.err.println( "SQLException: " + ex.getMessage() );
		}
	}

	public void rollback(){
		try{
			if( !connection.isClosed()){
				connection.rollback();
			}
		}catch ( Exception ex ){
			System.err.println( "SQLException: " + ex.getMessage() );
		}
	}
	
	public PreparedStatement getPs(String sql){
		try{
			return connection.prepareStatement(sql);
		}catch ( Exception ex ){
			System.err.println( "SQLException: " + ex.getMessage() );
		}
		return null;
	}
	
	public void close(){
		try{
			connection.close();
		}catch ( Exception ex ){
			System.err.println( "SQLException: " + ex.getMessage() );
		}
	}
	
	public void setSql(String sql){
		this.sql = sql;
	}
	
	public void addParam(String name, Object value){
		params.put(name, value);
	}
	
	public void clearParams(){
		params.clear();	
		paramsPosition.clear();
	}

	public ResultSet executeSQL(){
		ResultSet rs = null;
		open();
		PreparedStatement ps = getPs(parseSql());
		try {
		
			for( String paramName: params.keySet() ){
				Integer index = paramsPosition.indexOf(":"+paramName);
				while( index > -1  ){
					ps.setObject(index+1,params.get(paramName));
					paramsPosition.remove(paramsPosition.get(index));
					paramsPosition.add(index, "");
					index = paramsPosition.indexOf(":"+paramName);
				}
			}
		
			ps.execute();
			rs = ps.getResultSet();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		clearParams();
		close();
		return rs;
	}
	
	private String parseSql(){
		Pattern reg = Pattern.compile("(:[a-z_]*)");
		Matcher mat = reg.matcher(sql);
		while(  mat.find() ){
			paramsPosition.add(mat.group());
		}
		String s = mat.replaceAll("?");
		return s;
	}
	
	public static void main(String[] args){
		DBConection db = new DBConection();
		String sql = "select * from script.script " +
		"where id_tipo_script = :id_tipo_script " +
		"and week = :week " +
		"and id_script <> all( " +
		"select se.id_script from script.script_execucao se " +
		"inner join script.execucao e on (e.id_execucao = se.id_execucao) " +
		"inner join script.script s on (s.id_script = se.id_script) " +
		"where id_banco_dados = :id_banco_dados " +
		"and week = :week)";
		
		db.setSql(sql);
		db.addParam("id_tipo_script", 2);
		db.addParam("week", "WK-35");
		db.addParam("id_banco_dados", 1);
		db.executeSQL();
	}
	
}
